#include "dbmanager.h"
#include <QMessageBox>
#include <QDebug>
#include"tcpserver.h"
#include<QMutexLocker>
#include <QSqlError>
#include<QThread>
DBManager* DBManager::m_instance = nullptr;
QMutex DBManager::m_mutex;

DBManager::DBManager(QObject *parent)
    : QObject{parent}
{
}

DBManager *DBManager::getInstance()
{
    QMutexLocker locker(&m_mutex);
    if (!m_instance) {
        m_instance = new DBManager();
        m_instance->open("F://GitPro_2025_09_04//NetDisk//NetDiskServer//cloud.db","default_connection");
    }
    return m_instance;
}

void DBManager::release(){
    QMutexLocker locker(&m_mutex);
    if(m_instance)
    {
        delete m_instance;
        m_instance=nullptr;
    }
}

DBManager::~DBManager()
{
    m_db.close();
    qDebug()<<"DBManager析构函数";
}

bool DBManager::open(const QString dbName, const QString connectName)
{
    if (QSqlDatabase::contains(connectName))
    {
        QSqlDatabase::database(connectName).close();
    }
    // 建立和SQLite数据库的连接
    m_db = QSqlDatabase::addDatabase("QSQLITE", connectName);
    // 设置数据库文件的名字
    m_db.setDatabaseName(dbName);

    if(m_db.open()){
        m_query=QSqlQuery(m_db);
        qDebug()<<"数据库连接成功！";
        return true;
    }else{
        m_lastError=m_db.lastError().text();
        qDebug()<<"数据库连接失败！"<<m_lastError;
        return false;
    }
}

//1.处理用户注册
void DBManager::handleRegist(const QString& name,const QString& pwd,int tcpId)
{
    m_query.clear();
    if(name==NULL||pwd==NULL){
        emit registResult(name,false,tcpId);
        return;
    }
    QString sql=QString("insert into userInfo (name,pwd) values('%1','%2')").arg(name).arg(pwd);
    TcpServer::getInstance().logRecording("@@系统@@ :用户注册："+sql);
    bool res=m_query.exec(sql);
    emit registResult(name,res,tcpId);
}

//2.处理用户登录
void DBManager::handleLogIn(const QString&name, const QString&pwd,int tcpId)
{
    m_query.clear();
    if(name==NULL||pwd==NULL){
        emit loginResult(name,false,tcpId);
        return;
    }

    QString sql = QString("select * from userInfo where name = '%1' and pwd = '%2' and online = 0").arg(name).arg(pwd);
    TcpServer::getInstance().logRecording("@@系统@@ :查看用户是否在线："+sql);
    m_query.exec(sql);
    if(m_query.next())
    {
        sql = QString("update userInfo set online = 1 where name = '%1' and pwd = '%2' ").arg(name).arg(pwd);
        TcpServer::getInstance().logRecording("@@系统@@ :不在线时进行登录："+sql);
        bool res=m_query.exec(sql);
        emit loginResult(name,res,tcpId);
    }else{
        TcpServer::getInstance().logRecording("@@系统@@ :账号或密码错误或已登录");
        emit loginResult(name,false,tcpId);
    }
}

//3.处理用户下线
void DBManager::handleOffline(const QString &name,int tcpId)
{
    m_query.clear();
    if(name==NULL){
        emit usrOfflineResult(name,false,tcpId);
        return;
    }
    QString sql = QString("update userInfo set online = 0 where name = '%1'").arg(name);

    TcpServer::getInstance().logRecording(QString("@@系统@@ :用户[%1]下线："+sql).arg(name));
    bool res=m_query.exec(sql);
    emit usrOfflineResult(name,res,tcpId);
}

//4.返回所有在线用户
void DBManager::handleSearchAllOnlineUsrs(int tcpId)
{
    m_query.clear();
    QString sql = QString("select name FROM userInfo where online=1");
    TcpServer::getInstance().logRecording("@@系统@@ :获取所有在线用户："+sql);
    m_query.exec(sql);
    QStringList result;
    result.clear();
    while(m_query.next()){
        result.append(m_query.value(0).toString());
    }
    QString resultSplit=result.join(",");
    resultSplit="["+resultSplit+"]";
    TcpServer::getInstance().logRecording("@@系统@@ :在线用户集合："+resultSplit);
    emit onlineUsrsResult(result,tcpId);
}



//返回用户在线好友列表
void DBManager::handleSearchAllOnlineFriends(const QString &usrName, int tcpId)
{
    m_query.clear();
    QStringList strFriendList;
    strFriendList.clear();
    if(usrName==NULL)
    {
        emit onlineFriendsResult(strFriendList,tcpId);
        return;
    }

    // 获取请求方name对应的id
    QString sql = QString("select id from userInfo where name = \'%1\' and online = 1 ").arg(usrName);
    TcpServer::getInstance().logRecording("@@系统@@ :获取用户对应id："+sql);
    int id = -1; // 请求方name对应的id
    m_query.exec(sql);
    if (m_query.next())
    {
        id = m_query.value(0).toInt();
    }

    //查找所有在线好友
    sql=QString("select name from userInfo "
                  "where id in "
                  "(select friendId from friendInfo "
                  "where id = %1 "
                  " union "
                  "select id from friendInfo "
                  "where friendId = %2) and online= 1 ").arg(id).arg(id);
    TcpServer::getInstance().logRecording("@@系统@@ :获取所有在线好友列表："+sql);
    m_query.exec(sql);
    while(m_query.next()){
        strFriendList.append(m_query.value(0).toString());
    }
    QString resultSplit=strFriendList.join(",");
    resultSplit="["+resultSplit+"]";
    TcpServer::getInstance().logRecording("@@系统@@ :用户"+usrName+"在线好友集合："+resultSplit);
    emit onlineFriendsResult(strFriendList,tcpId);
    return;
}

void DBManager::handleOnlineForward(const QString &usrName, int tcpId)
{
    m_query.clear();
    QStringList strFriendList;
    strFriendList.clear();
    if(usrName==NULL)
    {
        emit onlineForwardFriends(strFriendList,tcpId);
        return;
    }

    // 获取请求方name对应的id
    QString sql = QString("select id from userInfo where name = \'%1\' and online = 1 ").arg(usrName);
    TcpServer::getInstance().logRecording("@@系统@@ :获取用户对应id："+sql);
    int id = -1; // 请求方name对应的id
    m_query.exec(sql);
    if (m_query.next())
    {
        id = m_query.value(0).toInt();
    }

    //查找所有在线好友
    sql=QString("select name from userInfo "
                  "where id in "
                  "(select friendId from friendInfo "
                  "where id = %1 "
                  " union "
                  "select id from friendInfo "
                  "where friendId = %2) and online= 1 ").arg(id).arg(id);
    TcpServer::getInstance().logRecording("@@系统@@ :获取所有在线好友列表："+sql);
    m_query.exec(sql);
    while(m_query.next()){
        strFriendList.append(m_query.value(0).toString());
    }
    QString resultSplit=strFriendList.join(",");
    resultSplit="["+resultSplit+"]";
    TcpServer::getInstance().logRecording("@@系统@@ :用户"+usrName+"通知上线好友集合："+resultSplit);

    emit onlineForwardFriends(strFriendList,tcpId);
    return;
}

void DBManager::handleOfflineForward(const QString &usrName, int tcpId)
{
    m_query.clear();
    QStringList strFriendList;
    strFriendList.clear();
    if(usrName==NULL)
    {
        emit offlineForwardFriends(usrName,strFriendList,tcpId);
        return;
    }

    // 获取请求方name对应的id
    QString sql = QString("select id from userInfo where name = \'%1\'").arg(usrName);
    TcpServer::getInstance().logRecording("@@系统@@ :获取用户对应id："+sql);
    int id = -1; // 请求方name对应的id
    m_query.exec(sql);
    if (m_query.next())
    {
        id = m_query.value(0).toInt();
    }

    //查找所有在线好友
    sql=QString("select name from userInfo "
                  "where id in "
                  "(select friendId from friendInfo "
                  "where id = %1 "
                  " union "
                  "select id from friendInfo "
                  "where friendId = %2) and online= 1 ").arg(id).arg(id);
    TcpServer::getInstance().logRecording("@@系统@@ :获取所有在线好友列表："+sql);
    m_query.exec(sql);
    while(m_query.next()){
        strFriendList.append(m_query.value(0).toString());
    }
    QString resultSplit=strFriendList.join(",");
    resultSplit="["+resultSplit+"]";
    TcpServer::getInstance().logRecording("@@系统@@ :用户"+usrName+"通知下线好友集合："+resultSplit);

    emit offlineForwardFriends(usrName,strFriendList,tcpId);
    return;
}

void DBManager::handleModifyUsrInfo(const QString &oldUsrName, const QString &newUsrName, const QString &newPwd, int currentId)
{
    m_query.clear();
    if(oldUsrName==NULL
        || newUsrName==NULL
        || newPwd==NULL
        ){
        emit usrModifyInfoResult(oldUsrName,false,currentId);
        return;
    }
    QString sql = QString("update userInfo set name = '%1' , pwd = '%2',online=0 where name = '%3'")
                      .arg(newUsrName).arg(newPwd).arg(oldUsrName);

    TcpServer::getInstance().logRecording(QString("@@系统@@ :用户 [%1] 改名为 [%2] :"+sql).arg(oldUsrName).arg(newUsrName));
    bool res=m_query.exec(sql);
    emit usrModifyInfoResult(oldUsrName,res,currentId);
}


//5.根据名称查找用户
void DBManager::handleSearchUsr(const QString &searchName,const QString &usrName,int tcpId)
{
    m_query.clear();
    QString sql =R"(
    SELECT
    u.id,
    u.name,
    u.online,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM friendInfo f
                JOIN userInfo s ON s.name = :usrName
                WHERE (f.id = u.id AND f.friendId = s.id)
                OR (f.id = s.id AND f.friendId = u.id)
                ) THEN 1
                ELSE 0
        END AS isFriend
    FROM userInfo u
    WHERE u.name LIKE '%' || :searchName || '%'
        AND u.name <> :usrName;
    )";

    m_query.prepare(sql);
    m_query.bindValue(":searchName", searchName);
    m_query.bindValue(":usrName", usrName);

    if (!m_query.exec()) {
        m_lastError=m_db.lastError().text();
        qDebug()<<"查找用户失败："<<m_lastError;
        return;
    }
    QList<UserInfo> resultList;
    while (m_query.next()) {
        UserInfo info;
        info.name = m_query.value("name").toString();
        info.online = m_query.value("online").toInt();
        info.isFriend = m_query.value("isFriend").toInt();
        resultList.append(info);
    }
    emit searchResultReady(resultList,tcpId);
}

//6.添加好友
void DBManager::handleAddFriend(const QString&addName, const QString&usrName,int tcpId,PDU*pdu)
{
    qDebug()<<"添加好友，数据库线程："<<QThread::currentThread();
    m_query.clear();
    if(addName==NULL || usrName==NULL)
    {
        emit addFriendResult(-1,tcpId,addName,pdu);
        return;          //系统错误
    }

    QString sql=QString("select *from friendInfo "
                  "where (id =(select id from userInfo where name='%1') "
                  "and friendId = (select id from userInfo where name='%2')) "
                  "or (id =(select id from userInfo where name='%3') "
                  "and friendId = (select id from userInfo where name='%4'))")
        .arg(usrName).arg(addName).arg(addName).arg(usrName);
    TcpServer::getInstance().logRecording("@@系统@@ :查看好友关系："+ sql);
    m_query.exec(sql);

    if(m_query.next()){
        TcpServer::getInstance().logRecording("@@系统@@ :双方已经为好友");
        emit addFriendResult(0,tcpId,addName,pdu);
        return;

    }else{
        sql = QString("select online from userInfo where name = '%1' ").arg(addName);
        TcpServer::getInstance().logRecording("@@系统@@ :查找用户："+sql);
        m_query.exec(sql);
        if(m_query.next()){
            int ret=m_query.value(0).toInt();
            if(ret==1){
                TcpServer::getInstance().logRecording("@@系统@@ :用户存在且在线");
                emit addFriendResult(1,tcpId,addName,pdu);
                return;
            }else if(ret==0){
                TcpServer::getInstance().logRecording("@@系统@@ :用户存在但不在线");
                emit addFriendResult(2,tcpId,addName,pdu);
                return;
            }
        }else{
            TcpServer::getInstance().logRecording("@@系统@@ :用户不存在");
            emit addFriendResult(3,tcpId,addName,pdu);
            return;
        }
    }
    TcpServer::getInstance().logRecording("@@系统@@ :系统错误");
    emit addFriendResult(-1,tcpId,addName,pdu);
    return;
}

//7.同意添加好友
void DBManager::handleAddFriendAgree(const QString &addName,const QString &usrName)
{
    m_query.clear();
    if(addName==NULL || usrName==NULL)
    {
        TcpServer::getInstance().logRecording("@@系统@@ :系统错误！");
        return;          //系统错误
    }

    int addId= getIdByUserName(addName);
    int usrId = getIdByUserName(usrName);
    QString sql=QString("insert into friendInfo (id,friendId) values (%1, %2) ").arg(usrId).arg(addId);
    TcpServer::getInstance().logRecording("@@系统@@ :插入数据库数据："+sql);
    m_query.exec(sql);
}

int DBManager::getIdByUserName(const QString&name)            //根据名字获取对应id
{
    m_query.clear();
    if(name==NULL)
    {
        return -1;
    }
    QString sql = QString("select id from userInfo where name = '%1' ").arg(name);
    TcpServer::getInstance().logRecording("@@系统@@ :根据用户名获取id："+sql);
    m_query.exec(sql);
    if(m_query.next())
    {
        return m_query.value(0).toInt();
    }
    else
    {
        return -1; // 不存在该用户
    }
}
//8.刷新好友列表
void DBManager::handleFlushFriend(const QString &usrName,int tcpId)
{
    m_query.clear();
    QStringList strFriendList;
    strFriendList.clear();
    if(usrName==NULL)
    {
        emit flushFriendRequest(strFriendList,tcpId);
        return;
    }

    // 获取请求方name对应的id
    QString sql = QString("select id from userInfo where name = \'%1\' and online = 1 ").arg(usrName);
    TcpServer::getInstance().logRecording("@@系统@@ :获取用户对应id："+sql);
    int id = -1; // 请求方name对应的id
    m_query.exec(sql);
    if (m_query.next())
    {
        id = m_query.value(0).toInt();
    }

    // 查询好友信息表与用户信息表获取好友列表
    sql=QString("select name, online from userInfo "
                  "where id in "
                  "(select friendId from friendInfo "
                  "where id = %1 "
                  " union "
                  "select id from friendInfo "
                  "where friendId = %2)").arg(id).arg(id);
    TcpServer::getInstance().logRecording("@@系统@@ :获取所有好友列表："+sql);
    m_query.exec(sql);
    while(m_query.next())
    {
        char friendName[32];
        char friOnline[4];
        strncpy(friendName, m_query.value(0).toString().toStdString().c_str(), 32);
        strncpy(friOnline, m_query.value(1).toString().toStdString().c_str(), 4);
        TcpServer::getInstance().logRecording("@@系统@@ :好友名称："+QString(friendName)+",在线状态："+QString(friOnline));
        strFriendList.append(friendName);
        strFriendList.append(friOnline);
    }
    emit flushFriendRequest(strFriendList,tcpId);
    return;
}

void DBManager::handleDelFriend(const QString&usrName,const QString&delName,int currentId,PDU*pdu)
{
    m_query.clear();
    if(usrName==NULL || delName==NULL)
    {
        emit delFriendRequest(false,currentId,delName,pdu);
        return;
    }

    QString sql = QString("delete from friendInfo "
                          "where id=(select id from userInfo where name = '%1') and friendId ="
                          "(select id from userInfo where name = '%2')").arg(usrName).arg(delName);

    TcpServer::getInstance().logRecording("@@系统@@ :删除好友关系1："+sql);
    m_query.exec(sql);

    sql = QString("delete from friendInfo "
                          "where id=(select id from userInfo where name = '%1') and friendId ="
                          "(select id from userInfo where name = '%2')").arg(delName).arg(usrName);

    TcpServer::getInstance().logRecording("@@系统@@ :删除好友关系2："+sql);
    bool result=m_query.exec(sql);
    emit delFriendRequest(result,currentId,delName,pdu);
}

void DBManager::handleGroupChatFriendList(const QString &usrName, int tcpId, PDU *pdu)
{
    m_query.clear();
    QStringList strFriendList;
    strFriendList.clear();
    if(usrName==NULL)
    {
        emit groupChatRequest(strFriendList,tcpId,pdu);
        return;
    }

    // 获取请求方name对应的id
    QString sql = QString("select id from userInfo where name = \'%1\' and online = 1 ").arg(usrName);
    TcpServer::getInstance().logRecording("@@系统@@ :获取用户对应id："+sql);
    int id = -1; // 请求方name对应的id
    m_query.exec(sql);
    if (m_query.next())
    {
        id = m_query.value(0).toInt();
    }

    // 查询好友信息表与用户信息表获取好友列表
    sql=QString("select name, online from userInfo "
                  "where id in "
                  "(select friendId from friendInfo "
                  "where id = %1 "
                  " union "
                  "select id from friendInfo "
                  "where friendId = %2)").arg(id).arg(id);
    TcpServer::getInstance().logRecording("@@系统@@ :获取所有好友列表："+sql);
    m_query.exec(sql);
    while(m_query.next())
    {
        char friendName[32];
        char friOnline[4];
        strncpy(friendName, m_query.value(0).toString().toStdString().c_str(), 32);
        strncpy(friOnline, m_query.value(1).toString().toStdString().c_str(), 4);
        TcpServer::getInstance().logRecording("@@系统@@ :好友名称："+QString(friendName)+",在线状态："+QString(friOnline));
        strFriendList.append(friendName);
        strFriendList.append(friOnline);
    }
    emit groupChatRequest(strFriendList,tcpId,pdu);
    return;
}



//用户注销
void DBManager::handleLogOff(const QString &name, int currentId)
{
    m_query.clear();
    int userId = -1;

    if (!m_db.transaction()) {
        TcpServer::getInstance().logRecording("@@系统@@ :开启事务失败:"+m_db.lastError().text());
        emit logoffResult(name,false,currentId);
        return;
    }

    // 1. 查找用户 id
    m_query.prepare("SELECT id FROM userInfo WHERE name = :name");
    m_query.bindValue(":name", name);
    if (!m_query.exec() || !m_query.next()) {
        TcpServer::getInstance().logRecording("@@系统@@ :未找到该用户或查询失败:"+m_query.lastError().text());
        m_db.rollback();
        emit logoffResult(name,false,currentId);
        return;
    }
    userId = m_query.value(0).toInt();


    // 2. 删除好友关联 (两边都可能有)
    m_query.prepare("DELETE FROM friendInfo WHERE id = :uid OR friendId = :uid");
    m_query.bindValue(":uid", userId);
    if (!m_query.exec()) {
        TcpServer::getInstance().logRecording("@@系统@@ :删除 friendInfo 失败:"+m_query.lastError().text());
        m_db.rollback();
        emit logoffResult(name,false,currentId);
        return;
    }

    // 3. 删除用户
    m_query.prepare("DELETE FROM userInfo WHERE id = :uid");
    m_query.bindValue(":uid", userId);
    if (!m_query.exec()) {
        TcpServer::getInstance().logRecording("@@系统@@ :删除 userInfo 失败:"+m_query.lastError().text());
        m_db.rollback();
        emit logoffResult(name,false,currentId);
        return;
    }

    // 4. 提交事务
    if (!m_db.commit()) {
        TcpServer::getInstance().logRecording("@@系统@@ :提交事务失败:"+m_db.lastError().text());
        m_db.rollback();
        emit logoffResult(name,false,currentId);
        return;
    }

    TcpServer::getInstance().logRecording("@@系统@@ :用户及好友关系删除成功, userId ="+QString::number(userId));
    emit logoffResult(name,true,currentId);
}


